﻿/***************************************************
 * 
 *  copyright (c) by ylshen
 * 
 *  author : ylshen (syl.lucky@gmail.com)
 *  
 * *************************************************/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace StockDataGeter
{
    /// <summary>
    /// 数据工具
    /// </summary>
    public class DataTool
    {
        public static void ShowGuideLine(string stockNO, int type, int year, int quarter, decimal value)
        {
            Console.WriteLine(String.Format("StockNO:{0} type:{1} year:{2} quarter:{3} value:{4}", stockNO, type, year, quarter, value));
        }
        /// <summary>
        /// 插入一个指标值
        /// </summary>
        /// <param name="stockNO">股票代码</param>
        /// <param name="type">指标类型</param>
        /// <param name="year">年份</param>
        /// <param name="quarter">季度</param>
        /// <param name="value">值</param>
        public static void InsertGuideLine(string stockNO, int type, int year, int quarter, decimal value)
        {
            SqlConnection cn = new SqlConnection(GetConString());
            string sql = "IF EXISTS (SELECT * FROM t_guideLines_" + type.ToString() + " WHERE stock = @stock AND year = @year AND quarter = @quarter)" + 
                " BEGIN " +
                " UPDATE t_guideLines_" + type.ToString() + " SET [value] = @value WHERE stock = @stock AND year = @year AND quarter = @quarter" + 
                " END " +
                " ELSE BEGIN " +
                "INSERT t_guideLines_" + type.ToString() + " (stock, [year], quarter,[value]) VALUES(@stock, @year, @quarter, @value)" + 
                " END ";
            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.Parameters.Add(new SqlParameter("@stock", stockNO));
            cmd.Parameters.Add(new SqlParameter("@year", year));
            cmd.Parameters.Add(new SqlParameter("@quarter", quarter));
            cmd.Parameters.Add(new SqlParameter("@value", value));

            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        

        /// <summary>
        /// 获取指定代码范围内的所有股票代码
        /// </summary>
        /// <param name="beginStock">股票代码开始范围</param>
        /// <param name="endStock">股票代码结束范围</param>
        /// <param name="stockType">股票类型（1　金融, 2非金融)</param>
        /// <returns></returns>
        public static string[] GetAllStocks(string beginStock, string endStock, int stockType)
        {
            SqlConnection cn = new SqlConnection(GetConString());
            string sql = String.Format("SELECT [no] FROM t_stocks WHERE kind = {0} AND [no] >= '{1}' AND [no] <= '{2}' ORDER BY [no]", stockType, beginStock, endStock);
            SqlCommand cmd = new SqlCommand(sql, cn);
            List<string> nos = new List<string>();
            cn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                nos.Add(reader.GetString(0));
            }
            reader.Close();
            cn.Close();
            return nos.ToArray();
        }

        public static void CreateGuideLineTables(int beginNO, int endNO)
        {
            SqlConnection cn = new SqlConnection(GetConString());
            cn.Open();
            for (int i = beginNO; i < endNO + 1; i++)
            {
                SqlCommand cmd = new SqlCommand(String.Format("create table t_guidelines_{0}(  id int IDENTITY(1,1) PRIMARY KEY,   stock  char(6),   [year] int,   [quarter] int,   [value] numeric(18, 2) )", i), cn);
                cmd.ExecuteNonQuery();
            }
            cn.Close();
        }

        public static void DeleteGuideLineData(int beginNO, int endNO)
        {
            SqlConnection cn = new SqlConnection(GetConString());
            cn.Open();
            for (int i = beginNO; i < endNO + 1; i++)
            {
                SqlCommand cmd = new SqlCommand(String.Format("Delete t_guidelines_{0} WHERE stock = '000515'", i), cn);
                cmd.ExecuteNonQuery();
            }
            cn.Close();
        }

        static string GetConString()
        {
            return System.Configuration.ConfigurationManager.AppSettings["conString"];
        }
    }
}
